﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class AB45Data : BaseFunction
    {


        public AB45Data()
            : base()
        {

        }
        public DataSet LoadTableStructure(DataSet dsTarget)
        {
            try
            {
                TableQueue.Enqueue("WAFARBEN");
                TableQueue.Enqueue("SELECT WAFART as ArtNum,	WAFVAR as ArtVar,	 WAFZUSATZ as ArtColor,WAFBEZ as ColorDescription"
                    + "  FROM INTEX.WAFARBEN WHERE RowNum<1");
                TableQueue.Enqueue("BSSATZ");
                TableQueue.Enqueue("SELECT  "
                    + "BSKART"
                    + ",BSFDZ"
                    + ",BSSRFNR"
                    + ",BSFAKTOR"
                   + " FROM INTEX.BSSATZ WHERE RowNum<1");
                TableQueue.Enqueue("BASTKE");
                TableQueue.Enqueue("SELECT "
                              + "BAKART"
               + ",BAKVAR"
               + ",BAKFARBE"
               + ",BAKKART"
               + ",BAKKVAR"
               + ",BAKKFARBE"
               + ",BAKPATT"
               + ",BAKPOS"
               + ",BAKANZ"
               + ",BAKABF"
               + " FROM INTEX.BASTKE WHERE RowNum<1");
                TableQueue.Enqueue("BSFARB");
                TableQueue.Enqueue("SELECT  "
                    + "BSFKART"
                    + ",BSFKVAR"
                    + ",BSFKFARBE "
                    + ",BSFBEZ"
                + " FROM INTEX.BSFARB WHERE RowNum<1");
                TableQueue.Enqueue("BASTGA");
                TableQueue.Enqueue("SELECT "
                + "BAGFDZ"
                + ",BAGART"
                + ",BAGVAR"
                + ",BAGFARBE"
                + ",BAGPOS"
                + ",BAGGART"
                + ",BAGGVAR"
                + ",BAGGFARBE"
                + ",BAGFDZ"
                + ",BAGKGNET"
                + ",BAGKGBRUT"
                + " FROM INTEX.BASTGA WHERE RowNum<1");
                TableQueue.Enqueue("WASTGA");
                TableQueue.Enqueue("SELECT  "
                       + "WAGART"
                + ",WAGVAR"
                + ",WAGWCOL"
                + ",WAGPSA"
                + ",WAGPOS"
                + ",WAGGART"
                + ",WAGGVAR"
                + ",WAGGFARBE"
                + ",WAGFDZ"
                + ",WAGKGNET"
                + ",WAGKGBRUT"
                + ",WAGEAFAKTOR"
                + ",WAGBEM"
                + " FROM INTEX.WASTGA WHERE RowNum<1");
                TableQueue.Enqueue("GSDAT");
                TableQueue.Enqueue("SELECT "
                + " GSART"
                + " ,GSVAR"
                + " ,GSMAT"
                + " ,GSNUMART"
                + " ,GSGARNNR"
                + " ,GSFACH"
                + " ,GSRECHNUM"
                + " ,GSRECHNR"
                 + " FROM INTEX.GSDAT WHERE RowNum<1");
                TableQueue.Enqueue("GASTGA");
                TableQueue.Enqueue("SELECT "
                    + "GAGART"
                    + ",GAGVAR"
                    + ",GAGFARBE"
                    + ",GAGPOS"
                    + ",GAGGART"
                    + ",GAGGVAR"
                    + ",GAGGFARBE"
                    + ",GAGKGNET"
                    + ",GAGKGBRUT"
                   + " FROM INTEX.GASTGA Where RowNum<1");
                TableQueue.Enqueue("GSCOLOR");
                TableQueue.Enqueue("SELECT GSCART,GSCVAR,GSCFARBE,GSCBEZ FROM INTEX.GSCOLOR WHERE RowNum<1");
                while (TableQueue.Count > 0)
                {
                    string TableName = TableQueue.Dequeue() as string;
                    string QueryStr = TableQueue.Dequeue() as string;
                    if (!dsTarget.Tables.Contains(TableName))
                    {
                        orlReadCommand = OracleDB.CreateCommand();
                        orlReadCommand.CommandText = QueryStr;
                        setResult = Connect2Proxy(orlReadCommand);
                        if (setResult.Tables.Count > 0)
                        {
                            setResult.Tables[0].TableName = TableName;
                        }
                        foreach (DataTable dtTemp in setResult.Tables)
                        {
                            dsTarget = ImportDataTable(dsTarget, dtTemp);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsTarget;
        }
        public AB45Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetBABST(DataRow dwABPOS)
        {
            string TableName = "BABST";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT BMBAUMKRS,BMBAUMLFD,BMKETTTYP,BMSOLLLG"
                    + "  FROM INTEX.BABST WHERE BMAUFKRS = :BMAUFKRS AND BMAUFLFD=:BMAUFLFD AND BMAUFPOS=:BMAUFPOS";
                orlReadCommand.Parameters.Add("BMAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("BMAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("BMAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    Queue Q10 = new Queue();
                    Q10.Enqueue("BMSOLLLG");
                    DivideBy10(setResult.Tables[TableName], Q10);
                    setResult.AcceptChanges();
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

        public DataSet GetGAAUF( DataRow dwABPOS)
        {
       
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            string TableName = "GAAUF";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT GSHANDLNR AS Composition,GAART, GAVAR,GAFARBE,GAKG1,GAKG3,'' as LOLBLP"
                    +",GPSPPART,GPFBPART,GPZWPART,GASPUL,'' as LSNAME1,GSCBEZ,GAPOS"
                    +" FROM INTEX.GAAUF LEFT JOIN INTEX.GSDAT ON GAART=GSART AND GAVAR=GSVAR"
                    +" LEFT JOIN GSCOLOR ON GSCART = GAART AND GSCVAR = GAVAR AND GSCFARBE = GAFARBE"
                    +" LEFT JOIN ZUTEI ON ZUAUFKRS=GAAUFKRS AND ZUAUFLFD=GAAUFLFD AND ZUAUFPOS=GAAUFPOS"
                    +" LEFT JOIN GPDAT ON GPPARTIE=ZUPARTIE"
                    + " WHERE GAAUFKRS = :GAAUFKRS AND GAAUFLFD=:GAAUFLFD AND GAAUFPOS=:GAAUFPOS";
                orlReadCommand.Parameters.Add("GAAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("GAAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("GAAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    Queue Q100 = new Queue();
                    Q100.Enqueue("GAKG1");
                    Q100.Enqueue("GAKG3");
                    DivideBy100(setResult.Tables[TableName], Q100);
                    setResult.AcceptChanges();
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
        public DataSet GetABFOLGE(DataRow dwABPOS)
        {
            //SELECT LSNAME1 FROM LIEFDAT WHERE LSLIEFNR='00000099'
            string TableName = "ABFOLGE";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ABFAFOLGE,ABFKSTKRS,ABFKST,ABFSPLPOS,ABFLIEFNR,LSNAME1,ABFAGANG,ABFBZAGANG,ABFMTYP,ABFSOLLSTART,ABFSOLLDAT"
                    + "  FROM INTEX.LIEFDAT,INTEX." + TableName + " WHERE " +
                    " LSLIEFNR=ABFLIEFNR AND " +
                " ABFAUFKRS = :ABFAUFKRS AND ABFAUFLFD=:ABFAUFLFD AND ABFAUFPOS=:ABFAUFPOS";
                orlReadCommand.Parameters.Add("ABFAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABFAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABFAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    setResult.AcceptChanges();

                    byte[] b;
                    string value;

                    foreach (DataRow dw in setResult.Tables[TableName].Rows)
                    {
                        foreach (DataColumn dc in setResult.Tables[TableName].Columns)
                        {
                            b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[dc.ColumnName].ToString().Trim());
                            value = Encoding.GetEncoding("gb2312").GetString(b);
                            dw[dc.ColumnName] = value; 
                        }
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetABPR(DataRow dwABPOS)
        {
            //SELECT LSNAME1 FROM LIEFDAT WHERE LSLIEFNR='00000099'
            string TableName = "ABPR";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT *"
                    + "  FROM ABPR "
                + " WHERE ARAUFKRS = :ARAUFKRS AND ARAUFLFD=:ARAUFLFD AND ARAUFPOS=:ARAUFPOS";
                orlReadCommand.Parameters.Add("ARAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ARAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ARAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    setResult.AcceptChanges(); 
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetABPOS(DataRow dwABPOS)
        {
            //"SELECT Distinct ABPAUFKRS ,ABPAUFLFD,ABPAUFPOS,ABPART,ABPVAR,ABPFARBE,ABPSTARTDAT,ABPENDDAT"
            //" FROM INTEX.ABPOS WHERE  ABPAUFART='VW' ";
            string TableName = "ABPOS";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ABPAUFKRS ,ABPAUFLFD,ABPAUFPOS,ABPART,ABPVAR,ABPFARBE,"
                + "ABPSTARTDAT,ABPENDDAT,BSFDZ,'x' as COMMENTS,'' AS BSFCOMM,ABPSOLLMENGE1 "
                    + "  FROM INTEX.BSSATZ,INTEX." + TableName + " WHERE "
                + " BSKART=ABPART AND "
                    + "ABPAUFKRS = :ABPAUFKRS AND ABPAUFLFD=:ABPAUFLFD AND ABPAUFPOS=:ABPAUFPOS";
                orlReadCommand.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                } 
                foreach (DataTable dtTemp in setResult.Tables)
                {
                    foreach (DataRow dwTmp in dtTemp.Rows)
                    {
                        string TXTISIND = dwTmp["ABPAUFKRS"].ToString() + dwTmp["ABPAUFLFD"].ToString() + dwTmp["ABPAUFPOS"].ToString();
                        dwTmp["COMMENTS"] = GetCommentsFromTXTIND("ABP", TXTISIND);
                        //BSF	011202              A12                                     ;
                        TXTISIND = dwTmp["ABPART"].ToString() + dwTmp["ABPVAR"].ToString().Trim() + dwTmp["ABPFARBE"].ToString();
                        TXTISIND = Left(TXTISIND + "                                                                                          ", 60);
                        dwTmp["BSFCOMM"] = GetCommentsFromTXTIND("BSF", TXTISIND);
                    } 
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetBASTGA(DataRow dwABPOS)
        {
            //"SELECT Distinct ABPAUFKRS ,ABPAUFLFD,ABPAUFPOS,ABPART,ABPVAR,ABPFARBE,ABPSTARTDAT,ABPENDDAT"
            //" FROM INTEX.ABPOS WHERE  ABPAUFART='VW' ";
            //BAGGART,BAGGVAR,BAGGFARBE,GSCBEZ,BAGFDZ,Composition
            string TableName = "BASTGA";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                /*
                orlReadCommand.CommandText = "SELECT DISTINCT GSHANDLNR AS Composition,GAART, GAVAR,GAFARBE," +
                "GAKG1,GAKG3,'' as LOLBLP,'' as ZUPARTIE,GASPUL,'' as GPSPPART,'' as LSNAME1,GSCBEZ,GAPOS"
                + "  FROM INTEX.GSDAT,INTEX.GAAUF "
                + " LEFT JOIN GSCOLOR ON GSCART = GAART AND GSCVAR = GAVAR AND GSCFARBE = GAFARBE"
                + " WHERE "
                + " GSART=GAART AND GSVAR=GAVAR AND "
                + " GAAUFKRS = :GAAUFKRS AND GAAUFLFD=:GAAUFLFD AND GAAUFPOS=:GAAUFPOS"
                + " ORDER BY GAPOS";*/
                orlReadCommand = OracleDB.CreateCommand();

                orlReadCommand.CommandText = "SELECT  DISTINCT BAGPOS,GSHANDLNR AS Composition, BAGGART,BAGGVAR,BAGGFARBE,GSCBEZ,BAGFDZ"
                    +"  FROM  INTEX.GSDAT,INTEX.BASTGA  LEFT JOIN GSCOLOR ON GSCART = BAGGART AND GSCVAR = BAGGVAR AND GSCFARBE = BAGGFARBE"
                    + " WHERE  GSART=BAGGART AND GSVAR=BAGGVAR AND BAGART=:BAGART AND BAGVAR = :BAGVAR AND BAGFARBE=:BAGFARBE"
                    + " ORDER BY BAGPOS";
                
                orlReadCommand.Parameters.Add("BAGART", OracleType.NVarChar).Value = dwABPOS["ABPART"].ToString();
                orlReadCommand.Parameters.Add("BAGVAR", OracleType.NVarChar).Value = dwABPOS["ABPVAR"].ToString().Substring(0,2);
                orlReadCommand.Parameters.Add("BAGFARBE", OracleType.NVarChar).Value = dwABPOS["ABPFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                } 
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetABAUFM( DataRow dwABPOS)
        {
            //"SELECT * FROM ABAUFM WHERE ABAAUFKRS='SB' AND ABAAUFLFD='000025'AND ABAAUFPOS='010' AND ABAAPOS>'0001'
            /*
             * SELECT ABPART,ABPVAR,ABPFARBE,LSNAME1,'0' as TotalSelvageEnds  FROM  INTEX.ABFOLGE LEFT JOIN LIEFDAT ON  LSLIEFNR=ABFLIEFNR, INTEX.ABPOS,INTEX.ABAUFM
WHERE  ABAAPOS>'0001'    AND ABAVKAUFKRS=ABPAUFKRS AND ABAVKAUFLFD=ABPAUFLFD AND ABAVKAUFPOS=ABPAUFPOS
AND ABFAUFKRS=ABPAUFKRS AND ABFAUFLFD=ABPAUFLFD AND ABFAUFPOS=ABPAUFPOS AND ABFKENNUNG='RP'
AND  ABAAUFKRS = :ABAAUFKRS AND ABAAUFLFD=:ABAAUFLFD AND ABPAUFPOS=:ABAAUFPOS
             */
            string TableName = "ABAUFM";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ABAVKAUFKRS||ABAVKAUFLFD||ABAVKAUFPOS as ReserveWeaveOrder,ABPART,ABPVAR,ABPFARBE,LSNAME1,'0' as TotalSelvageEnds  FROM  INTEX.ABFOLGE LEFT JOIN LIEFDAT ON  LSLIEFNR=ABFLIEFNR, INTEX.ABPOS,INTEX.ABAUFM"
                    + " WHERE  ABAAPOS>'0001'    AND ABAVKAUFKRS=ABPAUFKRS AND ABAVKAUFLFD=ABPAUFLFD AND ABAVKAUFPOS=ABPAUFPOS"
                    + " AND ABFAUFKRS=ABPAUFKRS AND ABFAUFLFD=ABPAUFLFD AND ABFAUFPOS=ABPAUFPOS AND ABFKENNUNG='RP'"
                    + "AND  ABAAUFKRS = :ABAAUFKRS AND ABAAUFLFD=:ABAAUFLFD AND ABPAUFPOS=:ABAAUFPOS";
                orlReadCommand.Parameters.Add("ABAAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABAAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABAAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVKPOS(DataRow dwABPOS)
        {

            string TableName = "VKPOS";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                //VKPKDARTNR
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM VKPOS WHERE VKPAUFKRS||VKPAUFLFD||VKPAUFPOS IN ("
                    +" SELECT abaVKaufkrs||ABAVKAUFLFD||ABAVKAUFPOS FROM ABAUFM WHERE ABAAUFKRS||ABAAUFLFD||ABAAUFPOS"
                    +" IN(SELECT abaVKaufkrs||ABAVKAUFLFD||ABAVKAUFPOS FROM ABAUFM WHERE ABAAUFKRS||ABAAUFLFD||ABAAUFPOS"
                    + " IN(select abaVKaufkrs||ABAVKAUFLFD||ABAVKAUFPOS from abaufm where ABAAUFKRS=:ABAAUFKRS And ABAAUFLFD=:ABAAUFLFD AND ABAAUFPOS=:ABAAUFPOS"
                    +" AND ABAVKAUFLFD<>'000000')AND ABAVKAUFLFD<>'000000')AND ABAVKAUFLFD<>'000000')";
                orlReadCommand.Parameters.Add("ABAAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABAAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABAAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWASTGA(DataRow dwABAUFM)
        {
            string TableName = "WASTGA";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WAGGART,WAGGVAR,WAGGFARBE,WAGFDZ,WAGKGBRUT,GSHANDLNR AS Composition,GSCBEZ FROM GSDAT,WASTGA"
                    +" LEFT JOIN GSCOLOR ON GSCART = WAGGART AND GSCVAR = WAGGVAR AND GSCFARBE = WAGGFARBE"
                    +" WHERE GSART=WAGGART AND GSVAR=WAGGVAR AND WAGPSA=1 AND WAGPOS='01' AND "
                    + " WAGART=:WAGART AND  Trim(WAGVAR)=:WAGVAR AND WAGWCOL=:WAGWCOL";
                orlReadCommand.Parameters.Add("WAGART", OracleType.NVarChar).Value = dwABAUFM["ABPART"].ToString();
                orlReadCommand.Parameters.Add("WAGVAR", OracleType.NVarChar).Value = dwABAUFM["ABPVAR"].ToString().Trim();
                orlReadCommand.Parameters.Add("WAGWCOL", OracleType.NVarChar).Value = dwABAUFM["ABPFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWTGEW(DataRow dwABAUFM)
        {
            string TableName = "WTGEW";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM WTGEW"
                    + " WHERE WTGART=:WTGART AND  Trim(WTGVAR)=:WTGVAR";
                orlReadCommand.Parameters.Add("WTGART", OracleType.NVarChar).Value = dwABAUFM["ABPART"].ToString();
                orlReadCommand.Parameters.Add("WTGVAR", OracleType.NVarChar).Value = dwABAUFM["ABPVAR"].ToString().Trim();
                
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetSELVAGEENDS(DataRow dwABAUFM)
        {
            // SELECT WAKLLEIST,WAKRLEIST,(WTLFDZ)  FROM WASTKE,WTLEIST  WHERE (WAKLLEIST=WTLLEIST OR WAKRLEIST=WTLLEIST)
            //AND (WAKART='PV912103' AND WAKVAR='A1')
            string TableName = "WASTKE";
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                #region leftedge
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WTLFDZ FROM INTEX.WASTKE,INTEX.WTLEIST  "
                + " WHERE WAKLLEIST=WTLLEIST "
                + " AND WAKART=:WAKART AND Trim(WAKVAR)=:WAKVAR AND WAKWCOL=:WAKWCOL";
                orlReadCommand.Parameters.Add("WAKART", OracleType.NVarChar).Value = dwABAUFM["ABPART"].ToString();
                orlReadCommand.Parameters.Add("WAKVAR", OracleType.NVarChar).Value = dwABAUFM["ABPVAR"].ToString().Trim();
                orlReadCommand.Parameters.Add("WAKWCOL", OracleType.NVarChar).Value = dwABAUFM["ABPFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                }
                setReturn.Merge(setResult);
                #endregion
                #region rightedge
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT WTLFDZ  FROM INTEX.WASTKE,INTEX.WTLEIST  "
                + " WHERE WAKRLEIST=WTLLEIST "
                + " AND WAKART=:WAKART AND Trim(WAKVAR)=:WAKVAR AND WAKWCOL=:WAKWCOL";
                orlReadCommand.Parameters.Add("WAKART", OracleType.NVarChar).Value = dwABAUFM["ABPART"].ToString();
                orlReadCommand.Parameters.Add("WAKVAR", OracleType.NVarChar).Value = dwABAUFM["ABPVAR"].ToString().Trim();
                orlReadCommand.Parameters.Add("WAKWCOL", OracleType.NVarChar).Value = dwABAUFM["ABPFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;  
                }
                setReturn.Merge(setResult); 
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetABKOPF( DataRow dwABPOS)
        {
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            string TableName = "ABKOPF";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT HRNNAME,HRDTELH,HRDFAX,ABKKDAUFTNR,ABKAEDAT,ABKAUFTTXT1,ABKAUFTTXT2"
                    + "  FROM INTEX.HRSTAMM, INTEX." + TableName
                    + " WHERE "
                    + " HRBENID=ABKARTSB"
                    + " AND ABKAUFKRS = :ABKAUFKRS AND ABKAUFLFD=:ABKAUFLFD";
                orlReadCommand.Parameters.Add("ABKAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABKAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    Queue Q100 = new Queue();
                    Q100.Enqueue("GAKG1");
                    //DivideBy100(dtTemp, Q100);
                    setResult.AcceptChanges();
                }
                
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

        public DataSet BuildProducitonOrderData(DataRow dwABPOS)
        {
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                setResult = GetABKOPF( dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetABPOS( dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetABAUFM( dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetGAAUF( dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetBABST( dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetABFOLGE( dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetABPR(dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetBASTGA(dwABPOS);
                setReturn.Merge(setResult);
                setResult = GetVKPOS(dwABPOS);
                setReturn.Merge(setResult);                
                DataRow dwABAUFM;
                if (setReturn.Tables.Contains("ABAUFM"))
                {
                    if (setReturn.Tables["ABAUFM"].Rows.Count > 0)
                    {
                        dwABAUFM = setReturn.Tables["ABAUFM"].Rows[0];

                        setResult = GetWASTGA(dwABAUFM);
                        setReturn.Merge(setResult);
                        setResult = GetWTGEW(dwABAUFM);
                        setReturn.Merge(setResult);
                        
                        setResult = GetSELVAGEENDS(dwABAUFM);
                        int TotalSelvage = 0;
                        foreach (DataRow dwTemp in setResult.Tables["WASTKE"].Rows)
                        {
                            TotalSelvage += System.Convert.ToInt16(dwTemp["WTLFDZ"].ToString());
                        } 
                        dwABAUFM["TotalSelvageEnds"] = TotalSelvage;                          
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}